package uk.ljmu.qudexapps.swfspreadsheet.utilities;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FilenameFilter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
//import java.net.FileNameMap;
//import java.net.URLConnection;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;

import org.apache.commons.fileupload.FileItemStream;
//import org.apache.commons.io.IOUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.tika.exception.TikaException;
import org.apache.tika.metadata.Metadata;
import org.apache.tika.parser.AutoDetectParser;
import org.apache.tika.parser.ParseContext;
import org.apache.tika.parser.Parser;
import org.apache.tika.sax.BodyContentHandler;
import org.springframework.web.multipart.MultipartFile;
import org.xml.sax.ContentHandler;
import org.xml.sax.SAXException;

import uk.ljmu.qudexapps.swfspreadsheet.domain.DdiObject;
import uk.ljmu.qudexapps.swfspreadsheet.domain.Files;
import uk.ljmu.qudexapps.swfspreadsheet.domain.MdSchemaInstance;
import uk.ljmu.qudexapps.swfspreadsheet.domain.VocabularyElement;

@SuppressWarnings({ "deprecation", "unused" })
public class ExcelManagement {
    final static int BUFFER = 2048;
    private List<String> fileList;
    private static Logger log = Logger.getLogger(ExcelManagement.class);
    private String fileSeparator;

    public void setListOfFiles(List<String> listOfFiles) {
	this.fileList = listOfFiles;
    }

    public List<String> getListOfFiles() {
	return fileList;
    }

    /** Creates a new instance of POIExcelReader */
    public ExcelManagement() {
	setListOfFiles(new ArrayList<String>());
	fileSeparator = System.getProperty("file.separator");
    }

    /**
     * This method is used to display the Excel content to command line.
     * 
     * @param xlsPath
     */
    public void displayFromExcel(String xlsPath) {
	InputStream inputStream = null;

	try {
	    inputStream = new FileInputStream(xlsPath);
	} catch (FileNotFoundException e) {
	    log.fatal("File not found in the specified path.");
	}

	POIFSFileSystem fileSystem = null;

	try {
	    fileSystem = new POIFSFileSystem(inputStream);

	    HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
	    HSSFSheet sheet = workBook.getSheetAt(0);
	    Iterator<Row> rows = sheet.rowIterator();

	    while (rows.hasNext()) {
		HSSFRow row = (HSSFRow) rows.next();
		// display row number in the console.
		System.out.println("Row No.: " + row.getRowNum());
		// once get a row its time to iterate through cells.
		Iterator<Cell> cells = row.cellIterator();

		while (cells.hasNext()) {
		    HSSFCell cell = (HSSFCell) cells.next();

		    /*
		     * Now we will get the cell type and display the values
		     * accordingly.
		     */
		    switch (cell.getCellType()) {
		    case HSSFCell.CELL_TYPE_NUMERIC: {
			// cell type numeric.
			System.out.println("Numeric value: "
				+ cell.getNumericCellValue());
			break;
		    }
		    case HSSFCell.CELL_TYPE_STRING: {
			// cell type string.
			HSSFRichTextString richTextString = cell
				.getRichStringCellValue();
			System.out.println("String value: "
				+ richTextString.getString());
			break;
		    }
		    default: {
			// types other than String and Numeric.
			System.out.println("Type not supported.");
			break;
		    }
		    }
		}
	    }
	} catch (IOException e) {
	    log.fatal(e.getMessage());
	}
    }

    /**
     * 
     * @param filePath
     * @param listSchemas
     * @param zipFile
     * @throws IOException
     */
    public boolean createExcelFile(OutputStream servletOut,
	    List<MdSchemaInstance> listSchemas,
	    int typeSS, Files files, String tempDir) throws IOException {
	boolean resultsOK;

	try {
	    // create a new workbook
	    HSSFWorkbook wb = new HSSFWorkbook();
	    // create a new sheet
	    HSSFSheet s = wb.createSheet();
	    HSSFRow r = null;

	    int rownum1 = 0;
	    int columnIndex = 0;

	    // Create the Headers Row
	    r = s.createRow(rownum1++);

	    if (listSchemas != null && listSchemas.size() > 0) {
		for (MdSchemaInstance schemaArray : listSchemas) {
		    if (!schemaArray.isRemoved())
			for (VocabularyElement elem : schemaArray
				.getSelectedSchemaElements()) {
			    if (elem.isSelected()) {
				HSSFCell cell = r.createCell(columnIndex,
					HSSFCell.CELL_TYPE_STRING);
				String name = schemaArray.getPrefix()
					.toLowerCase()
					+ ":" + elem.getName();
				cell.setCellValue(new HSSFRichTextString(name));
				columnIndex++;
			    }
			}
		}
	    }

	    if (typeSS == 1) {
		HSSFCell cell = r.createCell(columnIndex,
			HSSFCell.CELL_TYPE_STRING);
		// Check if ZIP file provided
		if (files.getZipFiles() != null && files.getZipFiles().size() > 0) {
		    String name = Constants.SOURCE_REFERENCE_M;
		    cell.setCellValue(new HSSFRichTextString(name));
		    int dsSourceRefColumn = columnIndex++;
		    //cell = r.createCell(columnIndex,
			//	HSSFCell.CELL_TYPE_STRING);
		    resultsOK = readZipFiles(s, files.getZipFiles(), dsSourceRefColumn, tempDir);
		    if (!resultsOK) {
			return false;
		    }
		} else {
		    String name = Constants.ORIGINAL_REFERENCE_R;
		    cell.setCellValue(new HSSFRichTextString(name));
		    autoFillRdfType(s, typeSS);
		}
	    } else {
	    	int indexUpdateDelete = findUpdateDelete(s);
			if (indexUpdateDelete != -1) {
			    HSSFCell cell = r.createCell(columnIndex++,
					HSSFCell.CELL_TYPE_STRING);
			    String name = Constants.IDENTIFIER_HEADER;
			    cell.setCellValue(new HSSFRichTextString(name));
			}
		//this.parseDDI(files.getZipFiles(), files.getDdiObjectList());
		// Auto fill DDI
		if (files.getDdiObjectList().size() > 0)
		    autoFillDDI(s, files.getDdiObjectList());
		else 
		    autoFillRdfType(s, typeSS);
		// Dropdown list for rdf:type cell
	    }
	    
	    // write the workbook to the output stream
	    // close our file (don't blow out our file handles
	    wb.write(servletOut);

	    return true;
	} catch (Exception e) {
	    log.fatal(e.getMessage());
	    return false;
	} finally {
	    // out.close();
	}
    }

	private void autoFillRdfType(HSSFSheet s, int typeSS) {
    	int rowIdx = 1;
    	int idxRdfType = this.findExcelElement(s, Constants.RDF_TYPE_HEADER);
    	HSSFRow row = s.createRow(rowIdx);
    	HSSFCell cell;
    	cell = row.createCell(idxRdfType,
    			HSSFCell.CELL_TYPE_STRING);
    	if (typeSS == 0)
    		cell.setCellValue(new HSSFRichTextString(Constants.typesMap.get(Constants.COLLECTION)));
    	else cell.setCellValue(new HSSFRichTextString(Constants.typesMap.get(Constants.FILE)));
    	CellRangeAddressList addressList = new CellRangeAddressList(cell.getRowIndex(), Constants.LIMIT_ROW_NUMBER, cell.getColumnIndex(), cell.getColumnIndex());
	    DVConstraint dvConstraint = (typeSS == 0) ? DVConstraint.createExplicitListConstraint(new String[]{Constants.typesMap.get(Constants.COLLECTION)}) :
	    	DVConstraint.createExplicitListConstraint(new String[]{Constants.typesMap.get(Constants.FILE)});
	    DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
	    dataValidation.setSuppressDropDownArrow(false);
	    dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
	    dataValidation.createErrorBox("Resource Type", "Select The value from the drop down list.");
	    s.addValidationData(dataValidation);
	    
	    //Autofill isOriginal with boolean validation
	    if (typeSS == 1) {
	    	int idxOrigType = this.findExcelElement(s, Constants.IS_ORIGINAL);
	    	cell = row.createCell(idxOrigType,
	    			HSSFCell.CELL_TYPE_STRING);
	    	cell.setCellValue(new HSSFRichTextString("false"));
	    	addressList = new CellRangeAddressList(cell.getRowIndex(), Constants.LIMIT_ROW_NUMBER, cell.getColumnIndex(), cell.getColumnIndex());
		    dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"true", "false"});
		    dataValidation = new HSSFDataValidation(addressList, dvConstraint);
		    dataValidation.setSuppressDropDownArrow(false);
		    s.addValidationData(dataValidation);
	    }
	}

	private void autoFillDDI(HSSFSheet s, List<DdiObject> ddiList) {
	// Find Fields in SS and autofill
	int idxDataC, idxTimeM, idxSampP, idxUniverse, idxCollMode, idxDesc;

	idxTimeM = this.findExcelElement(s, "ddi2:timeMethod");
	idxDataC = this.findExcelElement(s, "ddi2:dataCollector");
	idxSampP = this.findExcelElement(s, "ddi2:samplingProcedure");
	idxUniverse = this.findExcelElement(s, "ddi2:universe");
	idxCollMode = this.findExcelElement(s, "ddi2:collectionMode");
	idxDesc = this.findExcelElement(s, "dc:description");
	int rowIdx = 1;
	int idxRdfType = this.findExcelElement(s, Constants.RDF_TYPE_HEADER);
	for (DdiObject ddiObj : ddiList) {
	    HSSFRow row = s.createRow(rowIdx);
	    HSSFCell cell;
	    if (!ddiObj.getTimeMethod().equals("") && idxTimeM != -1) {
		cell = row.createCell(idxTimeM);
		cell.setCellValue(ddiObj.getTimeMethod());
	    }
	    if (!ddiObj.getDataCollector().equals("") && idxDataC != -1) {
		cell = row.createCell(idxDataC);
		cell.setCellValue(ddiObj.getDataCollector());
	    }
	    if (!ddiObj.getSamplingProcedure().equals("") && idxSampP != -1) {
		cell = row.createCell(idxSampP);
		cell.setCellValue(ddiObj.getSamplingProcedure());
	    }
	    if (!ddiObj.getUniverse().equals("") && idxUniverse != -1) {
		cell = row.createCell(idxUniverse);
		cell.setCellValue(ddiObj.getUniverse());
	    }
	    if (!ddiObj.getCollectionMode().equals("") && idxCollMode != -1) {
		cell = row.createCell(idxCollMode);
		cell.setCellValue(ddiObj.getCollectionMode());
	    }
	    if (!ddiObj.getDescription().equals("") && idxDesc != -1) {
			cell = row.createCell(idxDesc);
			cell.setCellValue(ddiObj.getDescription());
		    }
		if (idxRdfType != -1) {
			cell = row.createCell(idxRdfType,
			HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(Constants.typesMap.get(Constants.COLLECTION)));
			
			CellRangeAddressList addressList = new CellRangeAddressList(cell.getRowIndex(), Constants.LIMIT_ROW_NUMBER, cell.getColumnIndex(), cell.getColumnIndex());
		    DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{Constants.typesMap.get(Constants.COLLECTION)});
		    DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
		    dataValidation.setSuppressDropDownArrow(false);
		    dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
		    dataValidation.createErrorBox("Resource Type", "Select The value from the drop down list.");
		    s.addValidationData(dataValidation);
		}
		rowIdx++;
	}

    }

    private String[] getRDFType() {
    	String[] list = new String[2];
    	list[0] = Constants.typesMap.get(Constants.COLLECTION); 
    	list[1] = Constants.typesMap.get(Constants.FILE);
    	
		return list;
	}

	private boolean readZipFiles(HSSFSheet s, List<MultipartFile> zipFiles, int dsSourceRefColumn, String tempDir)
	    throws IOException {

	try {
	    // GET Sheet indexes
	    int formatColumnIndex = findExcelElement(s,
		    Constants.DC_FORMAT_HEADER);
	    if (formatColumnIndex == -1) {
		// Add the column
		HSSFRow row = s.getRow(0);
		HSSFCell cell = row.createCell((int) row.getLastCellNum());
		// Default value DATA:M
		cell.setCellValue(Constants.DC_FORMAT_HEADER);
		formatColumnIndex = cell.getColumnIndex();
	    }
	    // int identifierColumnIndex = findExcelElement(s,
	    // Constants.DC_ID_HEADER);
	    // if (identifierColumnIndex == -1) {
	    // Add the column
	    // HSSFRow row = s.getRow(0);
	    // HSSFCell cell = row.createCell((int) row.getLastCellNum());
	    // //Default value DATA:M
	    // cell.setCellValue(Constants.DC_ID_HEADER);
	    // identifierColumnIndex = cell.getColumnIndex();
	    // }
	    // PID Assigment
	    // int pidColumnIndex = findExcelElement(s,
	    // Constants.IDENTIFIER_HEADER);
	    boolean valSet = false, valSet2 = false;
	    int rowIndex = 1;
	    int indexIsOriginal = findIsOriginal(s);
	    for (MultipartFile file : zipFiles) {
	    // AMG Modification (find file in the temporary directory)
	    ZipFile zipFile = new ZipFile(tempDir + this.fileSeparator + file.getOriginalFilename());
		Enumeration<? extends ZipEntry> e = zipFile.entries();
		while (e.hasMoreElements()) {
			ZipEntry entry = (ZipEntry) e.nextElement();
		    if (entry.isDirectory()
			    || entry.getName().startsWith(
				    Constants.UNIX_HIDDEN_ID)
				||entry.getName().contains(this.fileSeparator +
					    Constants.UNIX_HIDDEN_ID)
			    || entry.getName().startsWith(
				    Constants.MAC_HIDDEN_FOLDER)) {
			// Either directory or hidden file
			continue;
		    }
		    // AMG Fill in File Format and other properties
		    List<String> attr = getTika(zipFile.getInputStream(entry), entry.getName());
		    HSSFRow row = s.createRow(rowIndex++);
		    HSSFCell cell;

		    // Fill in datastream name
		    cell = row.createCell(dsSourceRefColumn);
		    cell.setCellValue(entry.getName());
		    //Fill in qudex:isOriginal == FALSE
		    cell = row.createCell(indexIsOriginal, HSSFCell.CELL_TYPE_STRING);
		    cell.setCellValue(Boolean.TRUE.toString().toLowerCase());
		    //int idxOrigType = this.findExcelElement(s, Constants.IS_ORIGINAL);
	    	//cell = row.createCell(idxOrigType,
	    	//		HSSFCell.CELL_TYPE_STRING);
	    	if (!valSet2) {
		    	CellRangeAddressList addressList = new CellRangeAddressList(cell.getRowIndex(), Constants.LIMIT_ROW_NUMBER, cell.getColumnIndex(), cell.getColumnIndex());
			    DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"true", "false"});
			    DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
			    dataValidation.setSuppressDropDownArrow(false);
			    s.addValidationData(dataValidation); 
			    valSet2 = true;
		    }
	    	
		    // Fill in dc:format
		    cell = row.createCell(formatColumnIndex);
		    // Need to read the file
		    //FileNameMap fileNameMap = URLConnection.getFileNameMap();
		    //String mimeType = fileNameMap.getContentTypeFor(entry
			//    .getName());
		    if (attr != null) {
		    	if (attr.get(0) != null)
		    	cell.setCellValue(attr.get(0));
		    }
		    // Title
		    int titleColumnIndex = findExcelElement(s,
				    Constants.DC_TITLE_HEADER);
		    if (titleColumnIndex != -1 && attr != null) {
		    	 cell = row.createCell(titleColumnIndex);
		    	if (attr.get(1) != null)
		    	cell.setCellValue(attr.get(1));
		    }
		    // Author
		    int creatorColumnIndex = findExcelElement(s,
				    Constants.DC_CREATOR_HEADER);
		    if (creatorColumnIndex != -1 && attr != null) {
		    	cell = row.createCell(creatorColumnIndex);
		    	if (attr.get(2) != null)
		    	cell.setCellValue(attr.get(2));
		    }
		    // Language
		    int languageColumnIndex = findExcelElement(s,
				    Constants.DC_LANGUAGE_HEADER);
		    if (languageColumnIndex != -1 && attr != null) {
		    	cell = row.createCell(languageColumnIndex);
		    	if (attr.get(3) != null)
		    	cell.setCellValue(attr.get(3));
		    }
		    // Date
		    int dateColumnIndex = findExcelElement(s,
				    Constants.DC_DATE_HEADER);
		    if (dateColumnIndex != -1 && attr != null) {
		    	cell = row.createCell(dateColumnIndex);
		    	if (attr.get(4) != null)
		    	cell.setCellValue(attr.get(4));
		    }
		    // Description
		    int descColumnIndex = findExcelElement(s,
				    Constants.DC_DESC_HEADER);
		    if (descColumnIndex != -1 && attr != null) {
		    	cell = row.createCell(descColumnIndex);
		    	if (attr.get(5) != null)
		    	cell.setCellValue(attr.get(5));
		    }
		    // Subject
		    int subjectColumnIndex = findExcelElement(s,
				    Constants.DC_SUBJ_HEADER);
		    if (subjectColumnIndex != -1 && attr != null) {
		    	cell = row.createCell(subjectColumnIndex);
		    	if (attr.get(6) != null)
		    	cell.setCellValue(attr.get(6));
		    }
		    
		    // Lat
		    int latColumnIndex = findExcelElement(s,
				    Constants.GEO_LAT_LONG_HEADER);
		    if (latColumnIndex != -1 && attr != null) {
		    	cell = row.createCell(latColumnIndex);
		    	if (attr.get(7) != null && attr.get(8) != null)
		    	cell.setCellValue(attr.get(7) + "," + attr.get(8));
		    }
		    
		    int idxRdfType = this.findExcelElement(s, Constants.RDF_TYPE_HEADER);
			if (idxRdfType != -1) {
				cell = row.createCell(idxRdfType,
				HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(Constants.typesMap.get(Constants.FILE)));
				if (!valSet) {
					CellRangeAddressList addressList = new CellRangeAddressList(cell.getRowIndex(), Constants.LIMIT_ROW_NUMBER, cell.getColumnIndex(), cell.getColumnIndex());
				    DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{Constants.typesMap.get(Constants.FILE)});
				    DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
				    dataValidation.setSuppressDropDownArrow(false);
				    dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
				    dataValidation.createErrorBox("Resource Type", "Select The value from the drop down list.");
				    s.addValidationData(dataValidation);
				    valSet = true;
				}
			}
			int idxDcType = this.findExcelElement(s, Constants.DC_TYPE_HEADER);
			if (idxDcType != -1 && attr != null) {
				cell = row.createCell(idxDcType,
				HSSFCell.CELL_TYPE_STRING);
				if (attr.get(0) != null)
					cell.setCellValue(new HSSFRichTextString(Constants.typesMap.get(attr.get(0))));
			}
		    
		}
	    }
	} catch (Exception e) {
	    log.fatal(e.getMessage());
	    return false;
	}

	return true;
    }

    private int findIsOriginal(HSSFSheet sheet) {
	HSSFRow row = sheet.getRow(0);
	Iterator<Cell> iterator = row.cellIterator();
	while (iterator.hasNext()) {
	    Cell cell = iterator.next();
	    String name = String.valueOf(cell.getRichStringCellValue());
	    // Pattern either ':E' or ':M'
	    if (name.contains(Constants.IS_ORIGINAL))
		return cell.getColumnIndex();
	}
	// Not found
	return -1;
    }

    /**
     * 
     * @param filePath
     * @param listSchemas
     * @param listDatastreams
     */
    public void createExcelFileZipManagement(OutputStream servletOut,
	    List<MdSchemaInstance> listSchemas, String serverPath,
	    String temporaryDir) {
	// create a new file
	OutputStream out = null;
	try {
	    out = servletOut;
	} catch (Exception e1) {
	    log.fatal(e1.getMessage());
	}
	// create a new workbook
	HSSFWorkbook wb = new HSSFWorkbook();
	// create a new sheet
	HSSFSheet s = wb.createSheet();
	HSSFRow r = null;

	int rownum1 = 0;
	int columnIndex = 0;

	// create a row
	r = s.createRow(rownum1);
	HSSFCell c = r.createCell(columnIndex++, HSSFCell.CELL_TYPE_STRING);
	c.setCellValue(new HSSFRichTextString(Constants.IDENTIFIER_HEADER));

	for (MdSchemaInstance schemaArray : listSchemas) {
	    if (!schemaArray.isRemoved())
		for (VocabularyElement elem : schemaArray
			.getSelectedSchemaElements()) {
		    if (elem.isSelected()) {
			HSSFCell cell = r.createCell(columnIndex,
				HSSFCell.CELL_TYPE_STRING);
			String name = schemaArray.getPrefix().toLowerCase()
				+ Constants.HEADER_SEP + elem.getName();
			cell.setCellValue(new HSSFRichTextString(name));
			columnIndex++;
		    }
		}
	}
	// write the workbook to the output stream
	// close our file (don't blow out our file handles
	try {
	    wb.write(out);
	    out.close();
	} catch (IOException e) {
	    log.fatal(e.getMessage());
	}
    }

    public boolean decompressZipFile(String serverPath, FileItemStream zipFile)
	    throws IOException {
	ZipInputStream zis = null;
	BufferedOutputStream dest = null;

	try {
	    zis = new ZipInputStream(new BufferedInputStream(zipFile
		    .openStream()));
	    ZipEntry entry;

	    while ((entry = zis.getNextEntry()) != null) {
		int count;
		byte data[] = new byte[BUFFER];
		// GET REAL PATH
		String completeUrl = (serverPath.endsWith(fileSeparator)) ? serverPath
			+ entry.getName()
			: serverPath + fileSeparator + entry.getName();

		if (entry.isDirectory()) {
		    boolean dirCreated = new File(completeUrl).mkdir();
		    if (dirCreated)
			continue;
		    else
			return false;
		}

		// write the files to the disk
		FileOutputStream fos = new FileOutputStream(completeUrl);

		dest = new BufferedOutputStream(fos, BUFFER);
		while ((count = zis.read(data, 0, BUFFER)) != -1) {
		    dest.write(data, 0, count);
		}
		dest.flush();
		dest.close();
	    }

	    return true;
	} catch (Exception e) {
	    log.fatal(e.getMessage());
	    return false;
	} finally {
	    if (zis != null)
		zis.close();
	    if (dest != null)
		dest.close();
	}
    }

    public void processDirectory(File folder) {
	FilenameFilter filter = new FilenameFilter() {
	    public boolean accept(File dir, String name) {
		return !name.startsWith(".");
	    }
	};
	File[] listOfFiles = folder.listFiles(filter);

	for (int i = 0; i < listOfFiles.length; i++) {
	    if (listOfFiles[i].isFile()) {
		fileList.add(listOfFiles[i].getAbsolutePath());
	    } else if (listOfFiles[i].isDirectory()) {
		processDirectory(listOfFiles[i]);
	    }
	}
    }

    // SPREADSHEETS AUX FUNCTIONS
    /**
     * 
     * @param sheet
     * @param columnName
     * @return
     */
    private int findExcelElement(HSSFSheet sheet, String columnName) {

	HSSFRow row = sheet.getRow(0);
	Iterator<Cell> iterator = row.cellIterator();
	while (iterator.hasNext()) {
	    Cell cell = iterator.next();
	    String name = String.valueOf(cell.getRichStringCellValue());
	    if ((columnName.equalsIgnoreCase(name)))
		return cell.getColumnIndex();
	}
	// Not found
	return -1;
    }

    /**
     * 
     * @param sheet
     * @param columnName
     * @return
     */
    private int findDatastreamColumn(HSSFSheet sheet) {

	HSSFRow row = sheet.getRow(0);
	Iterator<Cell> iterator = row.cellIterator();
	while (iterator.hasNext()) {
	    Cell cell = iterator.next();
	    String name = String.valueOf(cell.getRichStringCellValue());
	    // Pattern either ':E' or ':M'
	    if (name.contains(Constants.DS_REDIRECT)
		    || name.contains(Constants.DS_MANAGED))
		return cell.getColumnIndex();
	}
	// Not found
	return -1;
    }
    
    /**
     * 
     * @param sheet
     * @param columnName
     * @return
     */
    private int findUpdateDelete(HSSFSheet sheet) {

	HSSFRow row = sheet.getRow(0);
	Iterator<Cell> iterator = row.cellIterator();
	while (iterator.hasNext()) {
	    Cell cell = iterator.next();
	    String name = String.valueOf(cell.getRichStringCellValue());
	    // Pattern either ':E' or ':M'
	    if (name.contains(Constants.UPDATE)
		    || name.contains(Constants.DELETE))
		return cell.getColumnIndex();
	}
	// Not found
	return -1;
    }
    
    private int findOriginalReferenceColumn(HSSFSheet sheet) {

	HSSFRow row = sheet.getRow(0);
	Iterator<Cell> iterator = row.cellIterator();
	while (iterator.hasNext()) {
	    Cell cell = iterator.next();
	    String name = String.valueOf(cell.getRichStringCellValue());
	    // Pattern either ':E' or ':M'
	    if (name.contains(Constants.ORIGINAL_REFERENCE_R)
		    || name.contains(Constants.ORIGINAL_REFERENCE_M))
		return cell.getColumnIndex();
	}
	// Not found
	return -1;
    }
    
    private int findSourceReferenceColumn(HSSFSheet sheet) {

	HSSFRow row = sheet.getRow(0);
	Iterator<Cell> iterator = row.cellIterator();
	while (iterator.hasNext()) {
	    Cell cell = iterator.next();
	    String name = String.valueOf(cell.getRichStringCellValue());
	    // Pattern either ':E' or ':M'
	    if (name.contains(Constants.SOURCE_REFERENCE_R)
		    || name.contains(Constants.ORIGINAL_REFERENCE_M))
		return cell.getColumnIndex();
	}
	// Not found
	return -1;
    }
    
	private List<String> getTika(InputStream is, String name) {
    	List<String> attr = new ArrayList<String>();
    	try {
			ContentHandler contenthandler = new BodyContentHandler();
	        Metadata metadata = new Metadata();
	        metadata.set(Metadata.RESOURCE_NAME_KEY, name);
	        Parser parser = new AutoDetectParser();
	        // OOXMLParser parser = new OOXMLParser();
	        parser.parse(is, contenthandler, metadata, new ParseContext());
	        //listAvailableMetaDataFields(metadata);
	        //Mimetype 0
	        attr.add(metadata.get(Metadata.CONTENT_TYPE));
	        //Title 1
	        attr.add(metadata.get(Metadata.TITLE));
	        //Author 2
	        attr.add(metadata.get(Metadata.AUTHOR));
	        //Language 3
	        attr.add(metadata.get(Metadata.LANGUAGE));
	        //Date 4
	        attr.add(metadata.get(Metadata.DATE));
	        //Description 5
	        attr.add(metadata.get(Metadata.DESCRIPTION));
	        //Subject 6
	        attr.add(metadata.get(Metadata.SUBJECT));
	        //Lat 7
	        attr.add(metadata.get(Constants.GEO_LAT_HEADER));
	        //Long 8 
	        attr.add(metadata.get(Constants.GEO_LONG_HEADER));
	        return attr;
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SAXException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (TikaException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return null;
    }
    
	private void listAvailableMetaDataFields(final Metadata metadata) {
      for(int i = 0; i <metadata.names().length; i++) {
        String name = metadata.names()[i];
        System.out.println(name + " : " + metadata.get(name));
      }
    }
}
